﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Configuration;

namespace HRdex_Final.Announcement
{
    public partial class WebForm2 : System.Web.UI.Page
    {
        DataTable dtbl = new DataTable("dtbl");
        SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\HRdexDB.mdf;Integrated Security=True;User Instance=True");
        //string conn = ConfigurationManager.ConnectionStrings["HRmainConnectionString"].ConnectionString;
        string SessionID()
        {

            return Session["EmployeeID"].ToString();
        }
        protected void Page_Load(object sender, EventArgs e)
        {
            SqlConnection conn2 = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\HRdexDB.mdf;Integrated Security=True;User Instance=True");
            SqlCommand cmd = new SqlCommand("SELECT formEmployees.EmployeeID, catUserLevel.catUserLevelName FROM formEmployees INNER JOIN catUserLevel ON formEmployees.UserLevelID = catUserLevel.UserLevelID WHERE formEmployees.EmployeeID = " + SessionID(), conn2);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable("dt");
            da.Fill(dt);
            if (dt.Rows[0].ItemArray[1].ToString() == "Faculty" ||dt.Rows[0].ItemArray[1].ToString() == "Employee")
            {
                btnEdt.Visible = false;
                btnSubmit.Visible = false;
            }

            lblConfirmation.Visible = false;
            string announcementID = Request.QueryString["announcementID"];
            if (announcementID == null)
            {
                txtAnnouncementID.Enabled = true;
                txtDescription.Enabled = true;
                txtSubject.Enabled = true;
                fuAttachment.Visible = true;
                ddlCategories.Enabled = true;
                btnDownload.Visible = false;
                
            }
            else
            {
                Select();
            }
        }



        protected void txtSubmit_Click(object sender, EventArgs e)
        {
            string announcementID = Request.QueryString["AnnouncementID"];
            //connection Add data
            SqlDataAdapter adapter = new SqlDataAdapter();
            DataSet ds = new DataSet();


            if (txtAnnouncementID.Text != announcementID)
            {
                conn.Open();
                //Add 
                string sqlc;
                
                
                Stream fs = fuAttachment.PostedFile.InputStream;
                BinaryReader br = new BinaryReader(fs);
                Byte[] bytes = br.ReadBytes((Int32)fs.Length);

                string filePath = fuAttachment.PostedFile.FileName;
                string fileType = Path.GetExtension(filePath);
                string fileName = Path.GetFileName(filePath);
                if (filePath != null)
                {
                   sqlc = "Insert Into formAnnouncement (Subject, Body, CatAnnID, Attachment, DateTime, AttachName, AttachType) " + 
                       "Values (@subject, @body, @catannID, @attachment,@datetime, @attachName, @attachType)";
                }
                else
                {
                    sqlc = "Insert Into formAnnouncement (Subject, Body, CatAnnID, DateTime) " +
                       "Values (@subject, @body, @catannID, @datetime)";
                }
                
                SqlCommand cmd = new SqlCommand(sqlc, conn);
                
                cmd.Parameters.Add("@subject", SqlDbType.VarChar).Value = txtSubject.Text;
                cmd.Parameters.Add("@body", SqlDbType.VarChar).Value = txtDescription.Text;
                cmd.Parameters.Add("@catannID", SqlDbType.Int).Value = ddlCategories.SelectedValue;
                cmd.Parameters.Add("@datetime", SqlDbType.DateTime).Value = DateTime.Now;
                

                if (filePath != null)
                {
                    cmd.Parameters.Add("@attachment", SqlDbType.Binary).Value = bytes;
                    cmd.Parameters.Add("@attachName", SqlDbType.VarChar).Value = fileName;
                    cmd.Parameters.Add("@attachType", SqlDbType.VarChar).Value = fileType;
                }

                cmd.ExecuteNonQuery();

                conn.Close();
                fuAttachment.Visible = true;
                btnDownload.Visible = false;
                lblConfirmation.Visible = true;
                lblConfirmation.Text = "You have successfully add new Announcement!";
            }
            else
            {
                EditHere();
                //uploadedit();
            }

            txtAnnouncementID.Enabled = false;
            txtDescription.Enabled = false;
            txtSubject.Enabled = false;
            fuAttachment.Visible = false;
            ddlCategories.Enabled = false;
        }

        override protected void OnInit(EventArgs e)
        {
            InitializeComponent();
            base.OnInit(e);
        }

        private void InitializeComponent()
        {
            //this.cmdStore.Click += new System.EventHandler(this.cmdStore_Click);
            this.Load += new System.EventHandler(this.Page_Load);

        }


        public void EditHere()
        {
            string announcementID = Request.QueryString["AnnouncementID"];
            string edit = ("UPDATE [formAnnouncement] SET [Subject] ='" + txtSubject.Text + "', [Body] ='" + txtDescription.Text + "' WHERE  ([AnnouncementID] = " + announcementID + ")");
            SqlCommand cmd2 = new SqlCommand(edit, conn);
            SqlDataAdapter adapt = new SqlDataAdapter(cmd2);
            conn.Open();

            
            cmd2.Parameters.AddWithValue("Subject", txtSubject.Text);
            cmd2.Parameters.AddWithValue("Body", txtDescription.Text);
            //cmd2.Parameters.AddWithValue("CatAnnID", ddlCategories.SelectedValue);

            cmd2.ExecuteNonQuery();
            conn.Close();
            fuAttachment.Visible = true;
            btnDownload.Visible = false;
            lblConfirmation.Visible = true;
            lblConfirmation.Text = "You have successfully Edited the Selected Announcement";
  
        }

        private void uploadedit()
        {
            string filepath = fuAttachment.PostedFile.FileName;
            string filename = Path.GetFileName(filepath);
            //lblDownload.Text = filename;

            Stream fs = fuAttachment.PostedFile.InputStream;
            BinaryReader br = new BinaryReader(fs);
            Byte[] bytes = br.ReadBytes((Int32)fs.Length);

            string announcementID = Request.QueryString["AnnouncementID"];
            string edit = ("UPDATE formAnnouncement SET  Attachment = @Data WHERE  ([AnnouncementID] = " + announcementID + ")");
            SqlCommand cmd = new SqlCommand(edit, conn);
            cmd.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes;
            SqlDataAdapter adapt = new SqlDataAdapter();

            conn.Open();

            cmd.ExecuteNonQuery();
            conn.Close();


        }

        protected void btnDownload_Click(object sender, EventArgs e)
        {
            string announcementID = Request.QueryString["AnnouncementID"];
            string strQuery = "SELECT Attachment, AttachName, AttachType FROM formAnnouncement WHERE AnnouncementID = " + announcementID;
            SqlCommand cmd = new SqlCommand(strQuery);
            DataTable dt = GetData(cmd);
            if (dt != null)
            {
                download(dt);
            }

        }
        private DataTable GetData(SqlCommand cmd)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter sda = new SqlDataAdapter();
            cmd.CommandType = CommandType.Text;
            cmd.Connection = conn;
            try
            {
                conn.Open();
                sda.SelectCommand = cmd;
                sda.Fill(dt);
                return dt;

            }
            catch
            {
                return null;
            }
            finally
            {
                conn.Close();
                sda.Dispose();
                conn.Dispose();
            }

        }
        private void download(DataTable dt)
        {
 
            Byte[] bytes = (Byte[])dt.Rows[0]["Attachment"];
            Response.Buffer = true;
            Response.Charset = "";
            Response.Cache.SetCacheability(HttpCacheability.NoCache);
            Response.ContentType = dt.Rows[0]["AttachType"].ToString();
            Response.AddHeader("content-disposition", "attachment;FileName="
            + dt.Rows[0]["AttachName"].ToString());
            Response.BinaryWrite(bytes);
            Response.Flush();
            Response.End();
        }

        public void Select()
        {
            string announcementID = Request.QueryString["AnnouncementID"];

            if (txtAnnouncementID.Text != announcementID)
            {
                SqlCommand cmd223 = new SqlCommand("SELECT Subject, Body, DateTime, Attachment, catAnnID FROM formAnnouncement WHERE AnnouncementID = " + announcementID ,conn);
                SqlDataAdapter da223 = new SqlDataAdapter(cmd223);
                DataTable dt = new DataTable("dt"); 
                da223.Fill(dt);
                txtAnnouncementID.Text = announcementID;
                //fuAttachment.FileName. = dtbl.Rows[0].ItemArray[3].ToString();
                txtDescription.Text = dt.Rows[0].ItemArray[1].ToString();
                lblPubDate.Text = dt.Rows[0].ItemArray[2].ToString();
                txtSubject.Text = dt.Rows[0].ItemArray[0].ToString();
                ddlCategories.SelectedValue = dt.Rows[0].ItemArray[4].ToString();

                //string announcementID = Request.QueryString["AnnouncementID"];
                string strQuery = "SELECT Attachment, AttachName, AttachType FROM formAnnouncement WHERE AnnouncementID = " + announcementID;
                SqlCommand cmd = new SqlCommand(strQuery);
                DataTable dt2 = GetData(cmd);
                if (dt2 != null)
                {
                    btnDownload.Visible = true;
                }
                else
                {
                    btnDownload.Visible = false;
                }


                txtAnnouncementID.Enabled = false;
                txtDescription.Enabled = false;
                txtSubject.Enabled = false;
                fuAttachment.Visible = false;
                ddlCategories.Enabled = false;
                //btnDownload.Visible = true;
            }

        }

        protected void btnEdit_Click(object sender, EventArgs e)
        {
            txtAnnouncementID.Enabled = true;
            txtDescription.Enabled = true;
            txtSubject.Enabled = true;
            ddlCategories.Enabled = true;
            fuAttachment.Visible = true;
            fuAttachment.Enabled = true;
            btnDownload.Visible = false;
        }

        
    }
}